﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Data;

namespace DigitalSolutionLibrary.SqlDatabase
{
    public class DatabaseWrapper
    {
        private string serverName;
        private string databaseName;
        private string userName;
        private string userPassword;
        private SqlConnection sqlConnection;

        public enum ReturnType
        {
            NONE,
            FIRST_CELL,
            NUM_ROW_AFFECT,
            DATASET
        }

        /// <summary>
        /// Create DatabaseWrapper to comunication with SQL server database
        /// </summary>
        /// <param name="serverName"></param>
        /// <param name="databaseName"></param>
        /// <param name="userName"></param>
        /// <param name="userPassword"></param>
        public DatabaseWrapper(string serverName, string databaseName, string userName, string userPassword)
        {
            this.databaseName = databaseName;
            this.serverName = serverName;
            this.userName = userName;
            this.userPassword = userPassword;
        }

        private bool OpenConnection()
        {
            try
            {
                sqlConnection = new SqlConnection();
                sqlConnection.ConnectionString = "Data Source = " + serverName + "; Network Library = DBMSSOCN; Initial Catalog = " + databaseName + "; User ID= " + userName + "; Password=" + userPassword;
                sqlConnection.Open();
                return true;
            }
            catch (ArgumentException arException)
            {
                Debug.WriteLine(arException.Message);
            }
            catch (InvalidOperationException inException)
            {
                Debug.WriteLine(inException.Message);
            }
            catch (SqlException sqlException)
            {
                Debug.WriteLine(sqlException.Message);
            }
            return false;
        }

        private bool CloseConnection()
        {
            try
            {
                sqlConnection.Close();
                return true;
            }
            catch (SqlException sqlException)
            {
                Debug.WriteLine(sqlException.Message);
            }
            return false;
        }

        /// <summary>
        /// Execute a store proceduce
        /// </summary>
        /// <param name="storeName"></param>
        /// <param name="paramStock">set null if store proceduce doesn't have parameter</param>
        /// <param name="returnType"></param>
        /// <returns>object data</returns>
        public object ExecuteStore(string storeName, ParameterStock paramStock, ReturnType returnType)
        {
            if (OpenConnection())
            {
                SqlCommand command = new SqlCommand(storeName, sqlConnection);
                command.CommandType = CommandType.StoredProcedure;

                if (paramStock != null)
                    command.Parameters.AddRange(paramStock.GetArrayParameter());

                switch (returnType)
                {
                    case ReturnType.DATASET:
                        SqlDataAdapter adapter = new SqlDataAdapter(command);
                        DataSet dataset = new DataSet();
                        adapter.Fill(dataset);
                        return dataset;
                    case ReturnType.FIRST_CELL:
                        try
                        {
                            return command.ExecuteScalar();
                        }
                        catch (SqlException sqlException)
                        {
                            Debug.WriteLine(sqlException.Message);
                        }
                        return null;
                    case ReturnType.NONE:
                    case ReturnType.NUM_ROW_AFFECT:
                        try
                        {
                            command.ExecuteNonQuery();
                        }
                        catch (SqlException sqlException)
                        {
                            Debug.WriteLine(sqlException.Message);
                        }
                        return null;
                }
            }
            return null;
        }

        /// <summary>
        /// Check connection to SQL server
        /// </summary>
        /// <param name="serverName"></param>
        /// <param name="databaseName"></param>
        /// <param name="userName"></param>
        /// <param name="userPassword"></param>
        /// <returns>true if connect success otherwise false</returns>
        public static bool CheckConnection(string serverName, string databaseName, string userName, string userPassword)
        {
            SqlConnection testConnection = new SqlConnection();
            try
            {
                testConnection.ConnectionString = "Data Source = " + serverName + "; Network Library = DBMSSOCN; Initial Catalog = " + databaseName + "; User ID= " + userName + "; Password=" + userPassword;
                testConnection.Open();
                testConnection.Close();
                return true;
            }
            catch (ArgumentException arException)
            {
                Debug.WriteLine(arException.Message);
            }
            catch (InvalidOperationException inException)
            {
                Debug.WriteLine(inException.Message);
            }
            catch (SqlException sqlException)
            {
                Debug.WriteLine(sqlException.Message);
            }
            return false;
        }
    }
}
